﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using WL_Store.Model;

namespace WL_Store.DAL
{
    /// <summary>
    /// 管理员信息查询
    /// </summary>
    public class EmployeeInfoDAL
    {
        /// <summary>
        /// 登录和查询个人信息
        /// </summary>
        /// <param name="number"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public EmployeeInfoModel EmployeeInfo(string loginName, string loginPassword)
        {
            string sql = "select*from AdminInfo where loginName=@loginName and loginPassword=@loginPassword;";
            //构造参数化查询
            SqlParameter[] parms = {
                new SqlParameter("@loginName", loginName),
                 new SqlParameter("@loginPassword", loginPassword),
            };

            #region ExecuteTable
            //  //拿到数据表
            //DataTable dt=  Dbhelper.ExecuteTable(sql,parms);
            //  //判断当前的数据表中的行数是否大于0
            //  if (dt.Rows.Count>0)
            //  {
            //    DataRow dr=dt.Rows[0];
            //      EmployeeInfoModel model = new EmployeeInfoModel();
            //      model.number = Convert.ToInt32(dr["number"]);
            //      model.name = dr["name"].ToString();
            //      model.sex = Convert.ToBoolean( dr["sex"]);
            //      model.idcard = dr["idcard"].ToString();
            //      model.phone = dr["phone"].ToString();
            //      model.email = dr["email"].ToString();
            //      model.address = dr["address"].ToString();
            //      model.entrytime = Convert.ToDateTime(dr["entrytime"]);
            //      model.regtime = Convert.ToDateTime(dr["regtime"]);
            //      return model;
            //  }
            //  return null;
            #endregion

            SqlDataReader readers = Dbhelper.ExecuteReader(sql, parms);
            //判断当前游标中是否有数据
            if (readers.HasRows)
            {
                //使游标向下移动一行
                if (readers.Read())

                {
                    //实例化model对象
                    EmployeeInfoModel model = new EmployeeInfoModel();
                    //为model对象赋值
                    model.number = readers["number"].ToString();
                    model.name = readers["name"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.age = Convert.ToInt32(readers["age"]);
                    model.idcard = readers["idcard"].ToString();
                    model.phone = readers["phone"].ToString();
                    model.email = readers["email"].ToString();
                    model.address = readers["address"].ToString();
                    model.entrytime = Convert.ToDateTime(readers["entrytime"]);
                    model.regtime = Convert.ToDateTime(readers["regtime"]);
                    model.loginName = readers["loginName"].ToString();
                    model.loginPassword = readers["loginPassword"].ToString();
                    //使用游标后关闭
                    readers.Close();
                    return model;
                }

            }

            return null;
        }

        /// <summary>
        /// 查询管理权限
        /// </summary>
        /// <returns></returns>
        public DataTable PermissionInfo()
        {
            string sql = "select*from Grades";
            return Dbhelper.ExecuteTable(sql);
        }


        /// <summary>
        /// 查询所有信息
        /// </summary>
        /// <returns></returns>
        //        public DataTable ALLEmployeeInfo()
        //        {
        //            //联合查询
        //            string sql = @"select i.number,i.name,
        //case i.sex
        //when 0 then '男'
        //when 1 then '女'
        //else '人妖' end
        //as sex
        //,i.age,i.idcard,i.phone,i.email,i.address,i.entrytime,i.regtime,i.loginName,i.loginPassword,a.Grade from EmployeeInfo as i inner join Authority as a on i.GradeId=a.GradeId
        //";

        //            return Dbhelper.ExecuteTable(sql);
        //        }


        public List<EmployeeInfoModel> GetEmployeeInfos(EmployeeInfoSearchModel searchModel)
        {
            //联合查询
            string sql = @"select i.number,i.name,
            i.sex,i.age,i.idcard,i.GradeId,i.phone,i.email,i.address,i.entrytime,i.regtime,i.loginName,i.loginPassword,a.Grade 
            from AdminInfo as i 
            inner join Authority as a on i.GradeId=a.GradeId";

            //构造where的泛型集合
            List<string> listWhere = new List<string>();
            //构造参数化查询数组的泛型集合
            List<SqlParameter> listParameters = new List<SqlParameter>();
            //判断多条件查询的条件管理员姓名是否输入
            if (!string.IsNullOrWhiteSpace(searchModel.Name))
            {
                listWhere.Add("i.name=@name");
                listParameters.Add(new SqlParameter("@name", searchModel.Name));
            }
            //判断多条件查询的条件管理员级别是否输入
            if (searchModel.GradeId > 0)
            {
                listWhere.Add("i.GradeId=@GradeId");
                listParameters.Add(new SqlParameter("@GradeId", searchModel.GradeId));
            }
            //判断多条件查询的条件管理员性别是否输入
            if (searchModel.Sex > -1)
            {
                listWhere.Add("i.sex=@Sex");
                listParameters.Add(new SqlParameter("@Sex", searchModel.Sex));
            }

            //判断入职时间(开始)是否为空
            if (searchModel.StartEntrytime.HasValue)
            {
                listWhere.Add("i.entrytime>=@StartEntrytime");
                listParameters.Add(new SqlParameter("@StartEntrytime", searchModel.StartEntrytime));

            }
            //判断入职时间(结束)是否为空
            if (searchModel.EndEntrytime.HasValue)
            {
                listWhere.Add("i.entrytime<=@EndEntrytime");
                listParameters.Add(new SqlParameter("@EndEntrytime", searchModel.EndEntrytime));

            }
            //判断检索管理员信息是否为空
            if (listWhere.Count > 0)
            {
                sql += " where " + string.Join(" and ", listWhere.ToArray());
            }

            SqlDataReader readers = Dbhelper.ExecuteReader(sql, listParameters.ToArray());

            //构造出一个泛型集合
            List<EmployeeInfoModel> list = new List<EmployeeInfoModel>();

            //判断游标中是否有数据行
            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    EmployeeInfoModel model = new EmployeeInfoModel();
                    model.number = readers["number"].ToString();
                    model.name = readers["name"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.age = Convert.ToInt32(readers["age"]);
                    model.Grade = readers["Grade"].ToString();
                    model.GradeId = Convert.ToInt32(readers["GradeId"]);
                    model.idcard = readers["idcard"].ToString();
                    model.phone = readers["phone"].ToString();
                    model.email = readers["email"].ToString();
                    model.address = readers["address"].ToString();
                    model.entrytime = Convert.ToDateTime(readers["entrytime"]);
                    model.regtime = Convert.ToDateTime(readers["regtime"]);
                    model.loginName = readers["loginName"].ToString();
                    model.loginPassword = readers["loginPassword"].ToString();
                    //追加到集合中
                    list.Add(model);
                }
                //关闭游标
                readers.Close();
            }
            return list;

        }


        /// <summary>
        /// 添加员工信息 返回：true 添加成功 返回：false 添加失败
        /// </summary>
        /// <param name="employeeInfo"></param>
        /// <returns></returns>
        public bool AddEmployeeInfos(EmployeeInfoModel employeeInfo)
        {
            string sql = @"insert into AdminInfo(
                        number,
                        name,
                        sex,
                        age,
                        GradeId,
                        idcard,
                        phone,
                        email,
                        address,
                        entryTime,
                        regTime,
                        loginName,
                        loginPassword) values(
                        @number,
                        @name,
                        @sex,
                        @age,
                        @GradeId,
                        @idcard,
                        @phone,
                        @email,
                        @address,
                        @entryTime,
                        @regTime,
                        @loginName,
                        @loginPassword)";
            //构造参数化查询数组
            SqlParameter[] parms = {
            new SqlParameter("@number",employeeInfo.number),
            new SqlParameter("@name",employeeInfo.name),
            new SqlParameter("@sex",employeeInfo.sex),
            new SqlParameter("@age",employeeInfo.age),
            new SqlParameter("@GradeId",employeeInfo.GradeId),
            new SqlParameter("@idcard",employeeInfo.idcard),
            new SqlParameter("@phone",employeeInfo.phone),
            new SqlParameter("@email",employeeInfo.email),
            new SqlParameter("@address",employeeInfo.address),
            new SqlParameter("@entryTime",DateTime.Now),
            new SqlParameter("@regTime",DateTime.Now),
            new SqlParameter("@loginName",employeeInfo.loginName),
            new SqlParameter("@loginPassword",employeeInfo.loginPassword)
            };
            return Dbhelper.ExecuteNonQuery(sql, parms) > 0;
        }


        /// <summary>
        ///删除管理员信息 返回true：删除成功；返回false：删除失败 
        /// </summary>
        /// <returns></returns>
        public bool DeleteEmployeeInfo(int number)
        {
            string sql = @"delete from AdminInfo where number=@number";
            SqlParameter[] parameters = {
            new SqlParameter("@number",number)
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        /// <summary>
        /// 
        /// 根据number查询信息
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public EmployeeInfoModel employee(string  number)
        {
            //查询字符串
            string sql = @"select*from AdminInfo where number=@number";
            //构造参数化查询数组
            SqlParameter[] parameters = {
            new SqlParameter("@number",number)
            };
            //调用dbhelper的ExecuteReader的方法
            SqlDataReader readers = Dbhelper.ExecuteReader(sql, parameters);

            if (readers.HasRows)
            {
                if (readers.Read())
                {
                    //实例化model对象
                    EmployeeInfoModel model = new EmployeeInfoModel(
                        );
                    //为model对象赋值
                    model.number = readers["number"].ToString();
                    model.name = readers["name"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.age = Convert.ToInt32(readers["age"]);
                    model.GradeId = Convert.ToInt32(readers["GradeId"]);
                    model.idcard = readers["idcard"].ToString();
                    model.phone = readers["phone"].ToString();
                    model.email = readers["email"].ToString();
                    model.address = readers["address"].ToString();
                    model.entrytime = Convert.ToDateTime(readers["entrytime"]);
                    model.regtime = Convert.ToDateTime(readers["regtime"]);

                    //使用游标后关闭
                    readers.Close();
                    return model;
                }

            }
            return null;
        }
        /// <summary>
        /// 根据number更改信息 返回ture：更改成功；返回false：更改失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Updateemployee(EmployeeInfoModel model)
        {
            string sql = @"update AdminInfo set name=@name,sex=@sex,age=@age,GradeId=@GradeId,idcard=@idcard,phone=@phone,email=@email,address=@address where number=@number";

            SqlParameter[] parameters = {
            new SqlParameter("@name",model.name),
            new SqlParameter("@sex",model.sex),
            new SqlParameter("@age",model.age),
            new SqlParameter("@GradeId",model.GradeId),
            new SqlParameter("@idcard",model.idcard),
            new SqlParameter("@phone",model.phone),
            new SqlParameter("@email",model.email),
            new SqlParameter("@address",model.address),
            new SqlParameter("@number",model.number)
            };

            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }



        #region PROC




        /// <summary>
        /// 添加信息（proc）
        /// </summary>
        /// <param name="employeeInfo"></param>
        /// <returns></returns>

        public bool AddEmployeeInfosProc(EmployeeInfoModel employeeInfo)
        {
            string procName = "PROC_AddAdminInfos";
            //构造参数化查询数组
            SqlParameter[] parms = {
            new SqlParameter("@number",employeeInfo.number),
            new SqlParameter("@name",employeeInfo.name),
            new SqlParameter("@sex",employeeInfo.sex),
            new SqlParameter("@age",employeeInfo.age),
            new SqlParameter("@GradeId",employeeInfo.GradeId),
            new SqlParameter("@idcard",employeeInfo.idcard),
            new SqlParameter("@phone",employeeInfo.phone),
            new SqlParameter("@email",employeeInfo.email),
            new SqlParameter("@address",employeeInfo.address),
            new SqlParameter("@entryTime",DateTime.Now),
            new SqlParameter("@regTime",DateTime.Now),
            new SqlParameter("@loginName",employeeInfo.loginName),
            new SqlParameter("@loginPassword",employeeInfo.loginPassword)
            };
            return Dbhelper.ExecuteNonQueryProc(procName, parms) > 0;
        }

        /// <summary>
        /// 查询所有信息（存储过程分页）
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public List<EmployeeInfoModel> GetEmployeeInfosProc(EmployeeInfoSearchModel searchModel, int PageIndex, int PageSize, out int TotalCount)
        {
            //为out赋初始值
            TotalCount = 0;
            //联合查询
            string procName = "PROC_QueryAllAdminInfos ";

            SqlParameter[] parameter = {
           new SqlParameter("@name",searchModel.Name),
           new SqlParameter("@GradeId",searchModel.GradeId),
           new SqlParameter("@sex",searchModel.Sex),
           new SqlParameter("@StartEntrytime",searchModel.StartEntrytime.HasValue?searchModel.StartEntrytime:(object)DBNull.Value),
           new SqlParameter("@EndEntrytime",searchModel.EndEntrytime.HasValue?searchModel.EndEntrytime:(object)DBNull.Value),
           new SqlParameter("@PageIndex",PageIndex),
           new SqlParameter("@PageSize",PageSize),
           new SqlParameter("@TotalCount",TotalCount)
            };
            //为存储过程中输入类型参数设定为output
            parameter[7].Direction = ParameterDirection.Output;

            SqlDataReader readers = Dbhelper.ExecuteReaderProc(procName, parameter);

            //构造出一个泛型集合
            List<EmployeeInfoModel> list = new List<EmployeeInfoModel>();

            //判断游标中是否有数据行
            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    EmployeeInfoModel model = new EmployeeInfoModel();
                    model.number = readers["number"].ToString();
                    model.name = readers["name"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.age = Convert.ToInt32(readers["age"]);
                    model.Grade = readers["Grade"].ToString();
                    model.GradeId = Convert.ToInt32(readers["GradeId"]);
                    model.idcard = readers["idcard"].ToString();
                    model.phone = readers["phone"].ToString();
                    model.email = readers["email"].ToString();
                    model.address = readers["address"].ToString();
                    model.entrytime = Convert.ToDateTime(readers["entrytime"]);
                    model.regtime = Convert.ToDateTime(readers["regtime"]);
                    model.loginName = readers["loginName"].ToString();
                    model.loginPassword = readers["loginPassword"].ToString();
                    //追加到集合中
                    list.Add(model);
                }
                //关闭游标
                readers.Close();
                //获取存储过程中的输出类型参数的值，一定要在游标关闭之后！！！！！
                TotalCount = Convert.ToInt32(parameter[7].Value);
            }
            return list;
        }



        /// <summary>
        /// 根据number更改信息 返回ture：更改成功；返回false：更改失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateemployeeProc(EmployeeInfoModel model)
        {
            string procName = @"PROC_UpdateAdminInfos";

            SqlParameter[] parameters = {
            new SqlParameter("@name",model.name),
            new SqlParameter("@sex",model.sex),
            new SqlParameter("@age",model.age),
            new SqlParameter("@GradeId",model.GradeId),
            new SqlParameter("@idcard",model.idcard),
            new SqlParameter("@phone",model.phone),
            new SqlParameter("@email",model.email),
            new SqlParameter("@address",model.address),
            new SqlParameter("@number",model.number)
            };

            return Dbhelper.ExecuteNonQueryProc(procName, parameters) > 0;
        }

        /// <summary>
        ///删除管理员信息 返回true：删除成功；返回false：删除失败 
        /// </summary>
        /// <returns></returns>
        public bool DeleteEmployeeInfoProc(string number)
        {
            string procName = "PROC_DeleteAdminInfos";
            SqlParameter[] parameters = {
            new SqlParameter("@number",number)
            };
            return Dbhelper.ExecuteNonQueryProc(procName, parameters) > 0;
        }

        #endregion




        /// <summary>
        /// 根据管理员编号修改管理员密码
        /// </summary>
        /// <returns></returns>
        public bool UpdatePassword(EmployeeInfoModel model)
        {
            string sql = "update AdminInfo set loginPassword=@loginPassword where number=@number";
            SqlParameter[] parameters = { 
            new SqlParameter("@loginPassword",model.loginPassword),
            new SqlParameter("@number",model.@number),
            };
            return Dbhelper.ExecuteNonQuery(sql,parameters) > 0;

        }

        /// <summary>
        /// 查询管理员编号在数据库中是否存在，存在返回1,不存在返回0；
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public object GetAdminNumber(string number)
        {
            string sql = @"select COUNT(*) from AdminInfo where number=@number";

            SqlParameter[] parameters = { 
           new SqlParameter("@number",number) 
            };
            return Dbhelper.ExecuteScalar(sql,parameters);
        }



    }
}
